package de.unistuttgart.iaas.interaction.mail;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Properties;

import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;

import de.unistuttgart.iaas.interaction.mail.database.MySQLAccess;
import de.unistuttgart.iaas.interaction.mail.services.GetRequest;
import de.unistuttgart.iaas.interaction.mail.util.CommonTools;
import de.unistuttgart.iaas.interaction.mail.util.ConfigFileHandler;
import de.unistuttgart.iaas.interaction.mail.util.LoggingFileHandler;
import de.unistuttgart.iaas.interaction.mail.util.TimeoutComputer;
/**
 * The main function of this class is used to handle all mails in the database with mail_state=SENT, AWAIT_REPLY or REPLIED.
 * @author Lina Sun
 *
 */

public class ProcessMail
{
	private static Logger logger = Logger.getLogger(ProcessMail.class);
	
	
	/**
	 * This method connects to the database and handle the mails with mail_state=SENT, AWAIT_REPLY or REPLIED.
	 */
	public static void ProcessMail(String configurationData)
	{		
		try
		{
			
			ConfigFileHandler config = new ConfigFileHandler(configurationData);
			LoggingFileHandler logFileHandler = new LoggingFileHandler(configurationData);
			
			Properties properties = new Properties();
			properties.setProperty("log4j.rootLogger", logFileHandler.getMailLogLevel()+", R");
			properties.setProperty("log4j.appender.R", logFileHandler.getMailAppender());
			properties.setProperty("log4j.appender.R.File", logFileHandler.getProcessMailLogFilePath());
			properties.setProperty("log4j.appender.R.MaxFileSize", logFileHandler.getMailLogFileSize() );
			properties.setProperty("log4j.appender.R.MaxBackupIndex", logFileHandler.getMailMaxBackupIndex());
			properties.setProperty("log4j.appender.R.layout", logFileHandler.getMailLayout());
			properties.setProperty("log4j.appender.R.layout.ConversionPattern", logFileHandler.getMailPattern());
			PropertyConfigurator.configure( properties );
			logger.info("---------------------ProcessMailService------------------------");	
			
			MySQLAccess dao = new MySQLAccess();
			Connection conn = dao.connDataBase(config);
			String getMail = "select * from maildb."+config.getTable_sentmails()+" where mail_state in ('SENT','AWAIT_REPLY','REPLIED')";
			
			//get the requests with mail_state = SENT, AWAIT_REPLY, REPLIED
			Statement statement = conn.createStatement();
			ResultSet rs = statement.executeQuery(getMail);
			
			while(rs.next())
			{	
				ArrayList mail = new ArrayList();
				
				for(int i=1; i<=11;i++)
				{
					mail.add(rs.getString(i));
				}
				
				String mail_state =(String) mail.get(10);
				
				if(mail_state.equalsIgnoreCase("SENT"))
				{
					ProcessSentMail(config,mail);
					
				}else if(mail_state.equalsIgnoreCase("AWAIT_REPLY"))
				{
					ProcessAwaitReplyMail(config,mail);
					
				}else if(mail_state.equalsIgnoreCase("REPLIED"))
				{
					ProcessRepliedMail(config, mail);
				}else
				{
					
				}
			}
			
			rs.close();
			statement.close();
			conn.close();
			
		}catch(Exception e)
		{
			//ErrorHandler
			logger.error("Error in ProcessMail().");
			System.out.println("Error in ProcessMail().");
		}
}

	/**
	 * This method handles the mails with mail_state=SENT. 
	 * It sends a mail.
	 * If this mail needs a response, then mail_state will be set to AWAIT_REPLY.
	 * If this mail doesn't need a response, then mail_state will be set to FINISHED.
	 * @param config 
	 * @param list_SentMail
	 */
	public static void ProcessSentMail(ConfigFileHandler config, ArrayList list_SentMail)
	{
		ArrayList list_AwaitReplyMail   = new   ArrayList(); 
		
		String mail_id = (String)list_SentMail.get(0);
		String mail_response = (String)list_SentMail.get(6);
		
		try
		{
			MySQLAccess dao = new MySQLAccess();
			Connection conn = dao.connDataBase(config);
			Statement update = conn.createStatement();
			
			
//			ResultSet rs = sentstatement.executeQuery("select * from maildb." + config.getTable_sentmails() + " where mail_id='"+ mail_id+"'");
//			
//			if(rs.next())
//			{
//				for(int i=1; i<=9;i++)
//				{
//					list_SentMail.add(rs.getString(i));
//				}
//			}
			

			if(mail_response.equalsIgnoreCase("no"))
			{
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='FINISHED' where mail_id='"+mail_id+"'");
				logger.info("mail_id "+ mail_id+"needs no response, set mail_state=finished.");
				System.out.println("mail_id "+ mail_id+"needs no response, set mail_state=finished.");
				
			}else if(mail_response.equalsIgnoreCase("yes"))
			{	
				logger.info("mail_id "+ mail_id +" needs response, set mail_state=await_reply.");
				System.out.println("mail_id "+ mail_id +" needs response, set mail_state=await_reply.");
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='AWAIT_REPLY' where mail_id='"+mail_id+"'");
				list_SentMail.set(10, "AWAIT_REPLY");
				list_AwaitReplyMail= list_SentMail;
				
				ProcessAwaitReplyMail(config, list_AwaitReplyMail); 
				
			}else
			{
				logger.error("Error in ProcessSentMail( )");
				System.out.println("Error1");
			}
			
			update.close();
			conn.close();
		}catch(Exception e)
		{
			//ErrorHandler
			logger.error("failed to process the request with mail_id: "+mail_id);
			System.out.println("failed to process the request with mail_id: "+mail_id);
		}
	}
	/**
	 * This method handles the mails with mail_state=AWAIT_REPLY. 
	 * If within the time limit service receive a response, then mail_state will be set to REPLIED.
	 * Otherwise mail_state will be set to TIMEOUT.
	 * @param config 
	 * @param list_AwaitReplyMail
	 */
	public static void ProcessAwaitReplyMail(ConfigFileHandler config, ArrayList list_AwaitReplyMail) throws Exception
	{

		MySQLAccess dao = new MySQLAccess();
		Connection conn = dao.connDataBase(config);
		Statement awaitStatement = conn.createStatement();
		Statement update = conn.createStatement();
		
		ArrayList list_RepliedMail = new ArrayList();
		
		boolean hasReply = false;
		
		String mail_id = (String)list_AwaitReplyMail.get(0);
		String mail_timeout =(String)list_AwaitReplyMail.get(8);
		
		
		ResultSet rsReply = awaitStatement.executeQuery("select * from maildb."+config.getTable_mailbox()+" where mail_replyid='"+mail_id+"' and mail_state='NEW'");
		
		while(rsReply.next())
		{
			
			hasReply = true;
			String receiveTime = rsReply.getString("mail_receivedDate");
			
			if(TimeoutComputer.isTimeout(mail_timeout, receiveTime))
			{
				//timeout
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='TIMEOUT' where mail_id='"+mail_id+"'");
				logger.info("mail_id "+mail_id+" is timeout, set mail_state= timeout, send timeout message back.");
				System.out.println("mail_id "+mail_id+" is timeout, set mail_state= timeout, send timeout message back.");
				
			}else
			{
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='REPLIED' where mail_id='"+mail_id+"'");
				
				list_AwaitReplyMail.set(10, "Replied");
				list_RepliedMail = list_AwaitReplyMail;
				
				ProcessRepliedMail(config,list_RepliedMail);	
				
			}
		}
		if(!hasReply)
		{
			Calendar cal = Calendar.getInstance();
			DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  			
			String now = df.format(cal.getTime());
			if(TimeoutComputer.isTimeout(mail_timeout, now))
			{
				//timeout
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='TIMEOUT' where mail_id='"+mail_id+"'");
				logger.info("mail_id "+mail_id+" is timeout, set mail_state= timeout, send timeout message back.");
				System.out.println("mail_id "+mail_id+" is timeout, set mail_state= timeout, send timeout message back.");
													
			}
		}
		
		rsReply.close();
		awaitStatement.close();
		update.close();
		conn.close();
		
	}
	
	/**
	 * This method handles the mails with mail_state=REPLIED.
	 * If the response is valid, then mail_state will be set to FINISHED.
	 * If the response is invalid within the time limit, then mail_state will be set to NEW. This mail will be resent.
	 * Otherwise mail_state will be set to TIMEOUT.
	 * 
	 * @param config
	 * @param list_RepliedMail
	 * @throws Exception
	 */

	public static void ProcessRepliedMail(ConfigFileHandler config, ArrayList list_RepliedMail) throws Exception
	{
		MySQLAccess dao = new MySQLAccess();
		Connection conn = dao.connDataBase(config);
		Statement repliedStatement = conn.createStatement();
		Statement update = conn.createStatement();
		
		ArrayList list_response = new ArrayList();
		
		String mail_id = (String)list_RepliedMail.get(0);
		String mail_responseTemplate = (String)list_RepliedMail.get(7);
//		System.out.println(mail_responseTemplate);
		String mail_timeout = (String)list_RepliedMail.get(8);
		ResultSet rsReply = repliedStatement.executeQuery("select * from maildb."+config.getTable_mailbox()+" where mail_replyid='"+mail_id+"' and mail_state='NEW'");

		boolean isValidResponse = false;
		
		while(rsReply.next())
		{
			for(int i=1; i<=10;i++)
			{
				list_response.add(rsReply.getString(i));
			}
			
			String repliedId = rsReply.getString("mail_id");
			//
			String res = CommonTools.getResponse(list_response);
			
			if(!res.equals(""))
			{
				isValidResponse = CommonTools.isValidResponse(config, res, mail_responseTemplate);
			}else
			{
				isValidResponse = false;
			}
			
										
			if(isValidResponse)
			{
				//valid response, request is finished, send response back.
				update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='FINISHED' where mail_id='"+mail_id+"'");
				update.executeUpdate("update maildb."+config.getTable_mailbox()+" set mail_state='FINISHED' where mail_id='"+repliedId+"'");
				Statement attStatement = conn.createStatement();
				ResultSet rs = attStatement.executeQuery("select filename from maildb."+config.getTable_mailattachments()+" where mail_id='"+mail_id+"'");
				String attachments ="";
				while(rs.next())
				{
					attachments = rs.getString("filename")+"\n";
				}
				logger.info("recieve a response of mail_ID: "+repliedId+"send this response("+res+") back.");
				System.out.println("recieve a response of mail_ID: "+repliedId+"send this response("+res+") back.");
				if(!attachments.equals(""))
				{
					logger.info("With attachment:\n"+ attachments);
					System.out.println("With attachment:\n"+ attachments);
				}
				
			}else
			{
				//invalid response
				Calendar cal = Calendar.getInstance();
				DateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  			
				String now = df.format(cal.getTime());
				if(TimeoutComputer.isTimeout(mail_timeout, now))
				{
					//timeout
					update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='TIMEOUT' where mail_id='"+mail_id+"'");
					update.executeUpdate("update maildb."+config.getTable_mailbox()+" set mail_state='FAILED' where mail_id='"+repliedId+"'");

					logger.info("mail_id "+mail_id+" is timeout, set mail_state= timeout, send timeout message back.");
					System.out.println("mail_id "+mail_id+" is timeout, set mail_state= timeout, send timeout message back.");
														
				}else
				{
					String mail_body="";
					Statement rsBodyStatement = conn.createStatement();
					ResultSet rsBody = rsBodyStatement.executeQuery("select mail_message from maildb."+config.getTable_sentmails()+" where mail_id='"+mail_id+"'");
					rsBody.next();
					mail_body = "Invalid response(s)!\n"+rsBody.getString("mail_message");
					//no timeout, resend the request, set new mail_sentDate
					update.executeUpdate("update maildb."+config.getTable_sentmails()+" set mail_state='NEW', mail_sentDate='"+now+"', mail_message='"+mail_body+"' where mail_id='"+mail_id+"'");
					update.executeUpdate("update maildb."+config.getTable_mailbox()+" set mail_state='FAILED' where mail_id='"+repliedId+"'");
					
					logger.info("Request(mail-id: "+mail_id+") will be resent.");
					System.out.println("Request(mail-id: "+mail_id+") will be resent.");
				}
			}
		}
		rsReply.close();
		repliedStatement.close();
		update.close();
	}
}